package com.sushe.dao;

import com.sushe.entity.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {

    private static final String url="jdbc:mysql://localhost:3306/sushe";
    private static final String username="root";
    private static final String password="123456";

    public  void addStudent(String name,String className) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  获取连接
        String sql="insert into student (name,class_name) values(?,?)";
        Connection connection= DriverManager.getConnection(url,username,password);
        //  创建statement
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setString(1,name);
        stmt.setString(2,className);
        int i=stmt.executeUpdate();
        System.out.println("SQL语句执行完毕影响的记录数为"+i);
        stmt.close();
        connection.close();
    }


    public  Integer getStudentId(String name) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select id from student where name=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setString(1,name);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            Integer Id = rs.getInt("id");
            return Id;
        }
        return null;
    }

    public  List<Student> showAllStudents() throws ClassNotFoundException, SQLException {
        List<Student> students=new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select * from student";
        Connection connection= DriverManager.getConnection(url, username, password);
        Statement stmt=connection.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next()) {
            Integer id = rs.getInt("id");
            String name=rs.getString("name");
            String className=rs.getString("class_name");

            Student student=new Student(id,name,className);

            students.add(student);
        }
        return students;
    }


    public  void deleteStudentById(Integer studentId) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  获取连接
        String sql="delete from student where id=?";
        Connection connection= DriverManager.getConnection(url,username,password);
        //  创建statement
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,studentId);

        int i=stmt.executeUpdate();
        System.out.println("SQL语句执行完毕影响的记录数为"+i);
        stmt.close();
        connection.close();
    }

    public List<Student> showAllNoBedStudents() throws ClassNotFoundException, SQLException {
        List<Student> students=new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select *\n" +
                "from student left join sushe.bed b on student.id = b.student_id\n" +
                "where b.id is null";
        Connection connection= DriverManager.getConnection(url, username, password);
        Statement stmt=connection.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next()) {
            Integer id = rs.getInt("id");
            String name=rs.getString("name");
            String className=rs.getString("class_name");

            Student student=new Student(id,name,className);

            students.add(student);
        }
        return students;
    }
}
